package apibasej.db.server.pool;

import java.sql.Array;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.NClob;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLClientInfoException;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.SQLXML;
import java.sql.Savepoint;
import java.sql.Statement;
import java.sql.Struct;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;
import java.util.logging.Level;

import apibasej.basic.misc.UtilTrace;
import apibasej.basic.pool.ItemPool;
import apibasej.db.server.ConstantsServerDB;

public class PooledConnection extends ItemPool implements Connection, ConstantsServerDB{

	// algumas funcionalidades foram baseadas no DBCP da jakarta
	
	private SimpleConnectionPool connectionPool;
	private Connection conn;
	
	private StackTraceElement[] createdBy;
	
	private String queryValidationConnection;

	private long creationTime;
	
	public PooledConnection(SimpleConnectionPool connectionPool) {
		this.connectionPool = connectionPool;
		this.creationTime = System.currentTimeMillis();
		verifyDebugCreate();
	}
	
	private void verifyDebugCreate(){
		if(getProp(DEBUG_TRANSACTION_TRACE)) {
			createdBy = Thread.currentThread().getStackTrace();
		}
	}
	
	public void setQueryValidationConnection(String queryValidationConnection) {
		this.queryValidationConnection = queryValidationConnection;
	}
	public String getQueryValidationConnection() {
		return queryValidationConnection;
	}
	public long getCreationTime() {
		return creationTime;
	}
	
	
	public String getInfo(){
		try{
			String dtHr = new SimpleDateFormat(getProp(PATTERN_DATE_TIME)+".SSS").format(new Date(creationTime));
			return "[connCreationTime='"+dtHr+"', isInvalidated()="+isInvalidated()+"]";
		}catch(Throwable e){// não deve ocorrer
			return "[ERRO_GET_INFO_CONN_connCreationTime="+creationTime+", isInvalidated()="+isInvalidated()+"]";
		}
	}
	
	/*
	public static void main(String[] args) throws SQLException {
        DriverManager.registerDriver(new org.postgresql.Driver());  
		try(
				Connection c = DriverManager.getConnection("jdbc:postgresql://localhost:5432/banco", "x", "x");
				PreparedStatement st = c.prepareStatement("SELECT 1 where 1=2");
				ResultSet rs = st.executeQuery();
				){
				rs.next();
				System.out.println("OK");
				//rs.close();// o novo try do java 7 já fecha
				//st.close();// o novo try do java 7 já fecha
			}catch (Throwable e) {
				e.printStackTrace();
			}
	}*/
	
	
	
	// verificar se a conexão está OK, pois o erro pode ter ocorrido por causa da conexão
	public void validadeConnection() throws SQLException{
		if(conn!=null && getQueryValidationConnection()!=null){
			try(
					
				//vai ficar sempre dando erro no postgre, pois quando da erro na operação qualquer operação depois para a conexão fica dando:
				//org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
					
				PreparedStatement st = conn.prepareStatement(getQueryValidationConnection());
				ResultSet rs = st.executeQuery();
				){
				rs.next();
				//rs.close();// o novo try do java 7 já fecha
				//st.close();// o novo try do java 7 já fecha
				//a conexão não deve ser fechada, e não fecha com este try, pois não foi criada aqui
			}
		}
	}
	
	
	@Override
	public void invalidate(){
		try{
			passivate();// para fazer rollback se for necessário
		}catch (Throwable e) {
			logDef(Level.SEVERE,"Error on passivate connection before invalidade",e);
		}finally{
			try {
				if(conn!=null && !conn.isClosed()) {
					conn.close();
				}
			} catch (Throwable e) {
				//precisa deste log ???
				// ver outras opções inves de lançar runtime exception
				//throw new APIBaseRuntimeException("Error on close connection", e);
				logDef(Level.SEVERE,"Error on close connection for PooledConnection",e);
			}finally{// invalida se conseguir ou não fechar a conexão
				//conn = null; // não setar null, pois ou outras threads podem chmar algum dos métodos abaixo, e NullPointer é feio
				super.invalidate();
			}
		}
	}
	
	@Override
	public void close(){
		try{
			passivate();// trata a conexão, antes de liberar para o pool
		}catch (Throwable e) {
			logDef(Level.SEVERE,"Error on passivate connection for close",e);
			invalidate();
		}finally{
			release();// libera no pool
		}
	}
	
	@Override
	public boolean isClosed(){ // throws SQLException 
		return !isBusy();
	}
	
	
	// copiado do DBCP e alterado ...
    private final void passivate() throws SQLException {
    	if(conn!=null && !conn.isClosed()){
            if(!conn.getAutoCommit() && !conn.isReadOnly()) {
            	conn.rollback();
            }
            conn.clearWarnings();
            if(!conn.getAutoCommit()) {// se não estiver auto commit seta como true, pois o default é ser automático
            	conn.setAutoCommit(true);
            }
    	}
    }
    // copiado do DBCP e alterado ...
    final void activate() throws SQLException {
    	
    	//DriverManager.setLoginTimeout(12);??? setar ao registrar o driver caso quira algum timeout
    	// o método activate deve ser chamado sempre antes de utilizar esta conexão
    	if(conn==null) {
    		//System.out.println(">>>ZZZ> "+connectionPool.getUrl()+"|"+connectionPool.getUser()+"|"+connectionPool.getPass());
    		conn = DriverManager.getConnection(connectionPool.getUrl(), connectionPool.getUser(), connectionPool.getPass());
    	}
        
    	if (conn.getAutoCommit() != connectionPool.isDefaultAutoCommit()) {
        	conn.setAutoCommit(connectionPool.isDefaultAutoCommit());
        }
    	
        int defaultTransactionIsolation = connectionPool.getDefaultTransactionIsolation();
        if ((defaultTransactionIsolation != SimpleConnectionPool.UNKNOWN_TRANSACTIONISOLATION) 
        		&& (conn.getTransactionIsolation() != defaultTransactionIsolation)) {
        	conn.setTransactionIsolation(defaultTransactionIsolation);
        }
        Boolean defaultReadOnly = connectionPool.getDefaultReadOnly();
        if ((defaultReadOnly != null) && (conn.isReadOnly() != defaultReadOnly.booleanValue())) {
        	conn.setReadOnly(defaultReadOnly.booleanValue());
        }
        String defaultCatalog = connectionPool.getDefaultCatalog();
        if ((defaultCatalog != null) && (!defaultCatalog.equals(conn.getCatalog()))) {
        	conn.setCatalog(defaultCatalog);
        }
    }
    
    // chamar depois do activate() ??? no getConnection() do ConnectionPool se tiver inválida chamar o invalidate() ...
	/*
	// copiado do DBCP
    private void validateConnection() throws SQLException {
    	Connection connection = getConn();
        if(conn.isClosed()) throw new SQLException("validateConnection: connection closed");
        if(null != connectionPool.getValidationQuery()) {
            Statement stmt = null;
            ResultSet rset = null;
            try {
                stmt = conn.createStatement();
                if (connectionPool.getValidationQueryTimeout() > 0)  stmt.setQueryTimeout(connectionPool.getValidationQueryTimeout());
                rset = stmt.executeQuery(connectionPool.getValidationQuery());
                if(!rset.next()) throw new SQLException("validationQuery didn't return a row");
            } finally {
                if (rset != null) try {rset.close();} catch(Exception t){} // ignored
                if (stmt != null) try {stmt.close();} catch(Exception t){} // ignored
            }
        }
    }*/
	
	@Override
	protected final void finalize() throws Throwable {
		// para validar se foi fechado corretamente
		if(conn!=null && !conn.isClosed()){// neste momento o close deve ter sido chamado
			String crBy = "";
			if(getProp(DEBUG_TRANSACTION_TRACE)) {
				crBy = "[Created by:\n"+UtilTrace.getStrTrace(createdBy)+"]";
			}
			logDef(Level.SEVERE,"### "+getClass().getName()+" was not closed! ("+this+") "+crBy+" ###",null);
			invalidate();
		}
	}
	
	
	
	//*******************************************************************
	
	
	@Override
	public void setReadOnly(boolean readOnly) throws SQLException {
		conn.setReadOnly(readOnly);
	}
	@Override
	public boolean isReadOnly() throws SQLException {
		return conn.isReadOnly();
	}
	@Override
	public void setAutoCommit(boolean autoCommit) throws SQLException {
		conn.setAutoCommit(autoCommit);
	}
	@Override
	public boolean getAutoCommit() throws SQLException {
		return conn.getAutoCommit();
	}
	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		return conn.unwrap(iface);
	}
	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		return conn.isWrapperFor(iface);
	}
	@Override
	public Statement createStatement() throws SQLException {
		return conn.createStatement();
	}
	@Override
	public PreparedStatement prepareStatement(String sql) throws SQLException {
		return conn.prepareStatement(sql);
	}
	@Override
	public CallableStatement prepareCall(String sql) throws SQLException {
		return conn.prepareCall(sql);
	}
	@Override
	public String nativeSQL(String sql) throws SQLException {
		return conn.nativeSQL(sql);
	}
	@Override
	public void commit() throws SQLException {
		conn.commit();
	}
	@Override
	public void rollback() throws SQLException {
		if(conn!=null) conn.rollback();
	}
	@Override
	public DatabaseMetaData getMetaData() throws SQLException {
		return conn.getMetaData();
	}
	@Override
	public void setCatalog(String catalog) throws SQLException {
		conn.setCatalog(catalog);
	}
	@Override
	public String getCatalog() throws SQLException {
		return conn.getCatalog();
	}
	@Override
	public void setTransactionIsolation(int level) throws SQLException {
		conn.setTransactionIsolation(level);
	}
	@Override
	public int getTransactionIsolation() throws SQLException {
		return conn.getTransactionIsolation();
	}
	@Override
	public SQLWarning getWarnings() throws SQLException {
		return conn.getWarnings();
	}
	@Override
	public void clearWarnings() throws SQLException {
		conn.clearWarnings();
	}
	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
		return conn.createStatement(resultSetType, resultSetConcurrency);
	}
	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		return conn.prepareStatement(sql, resultSetType, resultSetConcurrency);
	}
	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
		return conn.prepareCall(sql, resultSetType, resultSetConcurrency);
	}
	@Override
	public Map<String, Class<?>> getTypeMap() throws SQLException {
		return conn.getTypeMap();
	}
	@Override
	public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
		conn.setTypeMap(map);
	}
	@Override
	public void setHoldability(int holdability) throws SQLException {
		conn.setHoldability(holdability);
	}
	@Override
	public int getHoldability() throws SQLException {
		return conn.getHoldability();
	}
	@Override
	public Savepoint setSavepoint() throws SQLException {
		return conn.setSavepoint();
	}
	@Override
	public Savepoint setSavepoint(String name) throws SQLException {
		return conn.setSavepoint(name);
	}
	@Override
	public void rollback(Savepoint savepoint) throws SQLException {
		conn.rollback(savepoint);
	}
	@Override
	public void releaseSavepoint(Savepoint savepoint) throws SQLException {
		conn.releaseSavepoint(savepoint);
	}
	@Override
	public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return conn.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability);
	}
	@Override
	public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return conn.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
	}
	@Override
	public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
		return conn.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability);
	}
	@Override
	public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
		return conn.prepareStatement(sql, autoGeneratedKeys);
	}
	@Override
	public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
		return conn.prepareStatement(sql, columnIndexes);
	}
	@Override
	public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
		return conn.prepareStatement(sql, columnNames);
	}
	@Override
	public Clob createClob() throws SQLException {
		return conn.createClob();
	}
	@Override
	public Blob createBlob() throws SQLException {
		return conn.createBlob();
	}
	@Override
	public NClob createNClob() throws SQLException {
		return conn.createNClob();
	}
	@Override
	public SQLXML createSQLXML() throws SQLException {
		return conn.createSQLXML();
	}
	@Override
	public boolean isValid(int timeout) throws SQLException {
		return conn.isValid(timeout);
	}
	@Override
	public void setClientInfo(String name, String value) throws SQLClientInfoException {
		conn.setClientInfo(name, value); // a conn deve estar criada ...
	}
	@Override
	public void setClientInfo(Properties properties) throws SQLClientInfoException {
		conn.setClientInfo(properties); // a conn deve estar criada ...
	}
	@Override
	public String getClientInfo(String name) throws SQLException {
		return conn.getClientInfo(name);
	}
	@Override
	public Properties getClientInfo() throws SQLException {
		return conn.getClientInfo();
	}
	@Override
	public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
		return conn.createArrayOf(typeName, elements);
	}
	@Override
	public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
		return conn.createStruct(typeName, attributes);
	}

	@Override
	public void setSchema(String schema) throws SQLException {
		conn.setSchema(schema);
	}

	@Override
	public String getSchema() throws SQLException {
		return conn.getSchema();
	}

	@Override
	public void abort(Executor executor) throws SQLException {
		conn.abort(executor);
	}

	@Override
	public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
		conn.setNetworkTimeout(executor, milliseconds);
	}

	@Override
	public int getNetworkTimeout() throws SQLException {
		return conn.getNetworkTimeout();
	}

}
